#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   excel_geshi.py
@Time    :   2025/01/15
@Author  :   HDUZN
@Version :   1.0
@Contact :   hduzn@vip.qq.com
@License :   (C)Copyright 2024-2025
@Desc    :   设置Excel表格的格式
'''

# here put the import lib
import os
import pandas as pd
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill

# 获得当前项目目录
proj_path = os.getcwd()
print(f'proj_path: {proj_path}')

# 读取 test.xlsx 文件，把所有内容读取出来，生成一个新的文件 output_test.xlsx
ex_file = os.path.join(proj_path, 'test.xlsx')
df = pd.read_excel(ex_file)

output_file = os.path.join(proj_path, 'output_test.xlsx')

# 使用ExcelWriter设置格式
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
    df.to_excel(writer, index=False)

    # 获取工作表
    worksheet = writer.sheets['Sheet1']

    # 创建边框样式
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )

    # 设置标题行的行高
    worksheet.row_dimensions[1].height = 25  # 标题行高度设为25

    # 设置字体为Arial、居中对齐和边框
    for row in worksheet.iter_rows():
        for cell in row:
            cell.font = Font(name='Arial', size=11)
            cell.alignment = Alignment(horizontal='center', vertical='center')
            cell.border = thin_border

    # 设置F列标题的填充色为黄色
    f_cell = worksheet['F1']
    f_cell.fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

    # 设置E列所有有内容的单元格填充为橙色
    for cell in worksheet['E']:
        if cell.value:  # 如果单元格有内容
            cell.fill = PatternFill(start_color='FFA500', end_color='FFA500', fill_type='solid')

    # 设置B列所有有内容的单元格字体为红色
    for cell in worksheet['B']:
        if cell.value:  # 如果单元格有内容
            cell.font = Font(name='Arial', size=11, color='FF0000')

    # 设置列宽
    def get_column_width(text):
        # 计算字符串中中文和非中文字符的宽度
        width = 0
        for char in str(text):
            if '\u4e00' <= char <= '\u9fff':  # 中文字符
                width += 2.5
            else:  # 非中文字符
                width += 1
        return width

    for column in worksheet.columns:
        max_width = 0
        column_name = column[0].column_letter
        
        # 获取该列所有单元格的最大宽度
        for cell in column:
            try:
                cell_width = get_column_width(cell.value)
                if cell_width > max_width:
                    max_width = cell_width
            except:
                pass
        
        # 设置列宽（额外添加一些边距）
        adjusted_width = max_width + 4
        worksheet.column_dimensions[column_name].width = adjusted_width

    # 冻结首行
    worksheet.freeze_panes = 'A2'

print('-'*10, 'fine!')